/* ExcleCustomerToDB.java
 *
 * Project/Product: GW
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   Administrator	2008-2-21	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package system.imp;
import java.io.DataInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import system.base.ClientDownupfileInfo;
import system.base.Customer;
import system.base.InoutType;
import system.base.WarehouseCategory;
import framework.core.Conditions;
import framework.core.ConstDefine;
import framework.core.DBConnection;
import framework.core.ILoginContext;
import framework.core.LogWriter;
import framework.core.ObjectList;
/**
 * 导入Excel文件到数据库
 */
public class ExcelCustomerToDB implements GWBaseProcesser {
    //start chengbiao.he
    /*加入分类信息*/
    public static String doSaveToDB(DataInputStream aFile, DBConnection con, ILoginContext loginContext, String tableName, String warehouseCode,String upDownType)
            throws GWException

    {
        int tSuccCount = 0;
        int tFailCount = 0;
        String tFailFile = "0";
        HSSFWorkbook tHSSFWorkbook = new HSSFWorkbook();
        //开始读取excel文件
        HSSFSheet tHSSFSheet = null;
        try {
            tHSSFWorkbook = new HSSFWorkbook(aFile);
            tHSSFSheet = tHSSFWorkbook.getSheetAt(0);
        } catch (IOException e) {
            e.getStackTrace();
            new Exception("批量下载文件失败");
        }
        ArrayList alist = new ArrayList();
        ArrayList tsucceslist = new ArrayList();
        ArrayList tErrList = new ArrayList();

        //读取并检查Excel明细
        int tRowCount = parseExcelCustomer(alist, tHSSFSheet, loginContext);
        if (alist.size() > 0) {
            //保存上传文件到数据库
            for (int i = 0; i < alist.size(); i++) {
                try {
                    GWManufactureUploadVO gwManufactureUpload = (GWManufactureUploadVO) alist.get(i);
                    Customer cust = gwManufactureUpload.getCustomer();

                    //判断数据库中是否存在该条记录
                    //根据仓库编码和分类编码得到是否在数据库中存在该条信息
                    /*
                    Conditions cs = new Conditions();
                    cs.add(WarehouseCategory.WAREHOUSE_CODE,wc.getWarehouseCode());
                    cs.add(WarehouseCategory.CATEGORY_CODE,wc.getCategoryCode());
                    WarehouseCategory warehouseCategory = WarehouseCategory.fetch(con,cs);
                     */

                    Customer customer = Customer.fetch(con, cust.getId());
                    if (customer == null) {
                        //数据库中不存在的操作:得到ID = GUID值
                        customer = Customer.fetch();
                    }

                    //更新当前一条Excel信息数据库
                    customer.setId(cust.getId());
                    customer.setCode(cust.getCode());
                    customer.setSelfCode(cust.getSelfCode());
                    customer.setName(cust.getName());
                    customer.setFullName(cust.getFullName());
                    customer.setUpperCustomerCode(cust.getUpperCustomerCode());
                    customer.setAddress(cust.getAddress());
                    customer.setContactPerson(cust.getContactPerson());
                    customer.setContactTel(cust.getContactTel());
                    customer.setIsBalance(cust.getIsBalance());
                    customer.setIsDraw(cust.getIsDraw());
                    customer.setRemark(cust.getRemark());
                    customer.setWarehouseCode(cust.getWarehouseCode());
                    customer.setWarehouseName(cust.getWarehouseName());
                    customer.setStatus(cust.getStatus());
                    customer.setCreateUserId(cust.getCreateUserId());
                    customer.setCreateUser(cust.getCreateUser());
                    customer.setCreateTime(cust.getCreateTime());
                    customer.setLastUpdateUserId(cust.getLastUpdateUserId());
                    customer.setLastUpdateUser(cust.getLastUpdateUser());
                    customer.setLastUpdateTime(cust.getLastUpdateTime());
                    customer.save(con);
                    tSuccCount++;
                } catch (Exception e) {
                    System.out.println(e.getMessage());
                    tFailCount++;
                }
                
            }
            //在循环结束后在客户上传下载表中记录操作信息
            try {
                ClientDownupfileInfo clientDownupfileInfo = new ClientDownupfileInfo();
                ClientDownupfileInfo cdfi = ClientDownupfileInfo.fetch();
                cdfi.setCreateTime(new framework.core.DateUtil().getFullString());
                cdfi.setCreateUser(loginContext.getLoginUserName());
                cdfi.setCreateUserId(loginContext.getLoginUserId());
                cdfi.setDownloadtyp(upDownType);
                cdfi.setLastUpdateTime(new framework.core.DateUtil().getFullString());
                cdfi.setLastUpdateUser(loginContext.getLoginUserName());
                cdfi.setLastUpdateUserId(loginContext.getLoginUserId());
                cdfi.setParmsstr(tableName);
                cdfi.setParmstype(ClientDownupfileInfo.UPDOWN_STATE_TEXT);
                cdfi.setDatatableName(tableName);
                cdfi.setStatus(ConstDefine.STR_NAME_VALID_YES);
                cdfi.setUpdownState(ClientDownupfileInfo.UPDOWN_STATE_SUCCESS);
                cdfi.setWarehouseUincode(warehouseCode);
                cdfi.save(con);
            } catch (Exception e) {

            }

        }
        //读取sheet表单
        return tSuccCount + "-" + tFailCount + "-" + tRowCount;
    }

    //end chengbiao.he

    /*start chengbiao.he*/
    //解析Excel文件    
    private static int parseExcelCustomer(ArrayList aList, HSSFSheet aHSSFSheet, ILoginContext loginContext) throws GWException {
        int i = 0;
        //取得第一行的对象
        HSSFRow tFirstRow = aHSSFSheet.getRow(0);
        DecimalFormat a = new DecimalFormat("#");
        while (true) {
            HSSFRow tHSSFRow = aHSSFSheet.getRow(1 + i);
            boolean flag = false;
            try {

                for (int j = 0; j < 21; j++) {
                    if (tHSSFRow == null) {
                        flag = false;
                        break;
                    }
                    if (tHSSFRow.getCell((short) j) == null) {
                        break;
                    }
                    flag = true;
                    break;
                }
                if (!flag) {
                    break;
                } else {
                    GWManufactureUploadVO tGSManufactureUploadVO = new GWManufactureUploadVO();
                    int tResult = 1;
                    String tFailReason = "";

                    Customer cust = new Customer();
                     
                    for (int j = 0; j < 21; j++) 
                    {
                        //获得EXCEL首行记录
                        String tFiledName = PoiUtil.getCellValue(tFirstRow, j, "");//tFirstRow.getCell((short) j).getStringCellValue();
                        if (tFiledName == null) tFiledName = ""; 
                        //ID
                        if (tFiledName.equalsIgnoreCase(Customer.ID)) {
                            String tID = PoiUtil.getCellValue(tHSSFRow, j, "");//String.valueOf(tHSSFRow.getCell((short) j).getStringCellValue());
                            cust.setId(tID);
                        }
                        //仓库编码
                        else if (tFiledName.equalsIgnoreCase(Customer.CODE)) {
                            String tCode = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue().trim();
                            cust.setCode(tCode);
                        }
                        //仓库名称
                        else if (tFiledName.equalsIgnoreCase(Customer.SELF_CODE)) {
                            String tSelfCode= PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue().trim();
                            cust.setSelfCode(tSelfCode);
                        }
                        //分类编码
                        else if (tFiledName.equalsIgnoreCase(Customer.NAME)) {
                            String tName= PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue().trim();
                            cust.setName(tName);
                             
                        }
                        //分类名称 
                        else if (tFiledName.equalsIgnoreCase(Customer.FULL_NAME)) {
                            
                            String tFullName = PoiUtil.getCellValue(tHSSFRow, j, "");//String.valueOf(tHSSFRow.getCell((short) j).getStringCellValue());
                            cust.setFullName(tFullName);
                            
                            
                        }
                        //上级分类编码 
                        else if (tFiledName.equalsIgnoreCase(Customer.UPPER_CUSTOMER_CODE)) {
                            String tUpperCustomerCode = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue().trim();
                            cust.setUpperCustomerCode(tUpperCustomerCode);

                        }
                        //是否有效 
                        else if (tFiledName.equalsIgnoreCase(Customer.ADDRESS)) {
                            String tAddress = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue();
                            cust.setAddress(tAddress);
                        }
                        //分类编码
                        else if (tFiledName.equalsIgnoreCase(Customer.CONTACT_PERSON)) {
                            String tContactPerson = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue().trim();
                            cust.setContactPerson(tContactPerson);

                        }
                        //分类名称 
                        else if (tFiledName.equalsIgnoreCase(Customer.CONTACT_TEL)) {
                            String tContactTel = PoiUtil.getCellValue(tHSSFRow, j, "");//String.valueOf(tHSSFRow.getCell((short) j).getStringCellValue());
                            cust.setContactTel(tContactTel);
                        }
                        //上级分类编码 
                        else if (tFiledName.equalsIgnoreCase(Customer.IS_BALANCE)) {
                            String tIsBalance = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue().trim();
                            cust.setIsBalance(tIsBalance);

                        }
                        //是否有效 
                        else if (tFiledName.equalsIgnoreCase(Customer.IS_DRAW)) {
                            String tIsDraw = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue();
                            cust.setIsDraw(tIsDraw);
                        }
                        //创建用户ID
                        else if (tFiledName.equalsIgnoreCase(Customer.REMARK)) {
                            String tRemark = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue();
                            cust.setRemark(tRemark);
                        }
                        //分类名称 
                        else if (tFiledName.equalsIgnoreCase(Customer.WAREHOUSE_CODE)) {
                            String tWarehouseCode = PoiUtil.getCellValue(tHSSFRow, j, "");//String.valueOf(tHSSFRow.getCell((short) j).getStringCellValue());
                            cust.setWarehouseCode(tWarehouseCode);
                        }
                        //上级分类编码 
                        else if (tFiledName.equalsIgnoreCase(Customer.WAREHOUSE_NAME)) {
                            String tWarehouseName = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue().trim();
                            cust.setWarehouseName(tWarehouseName);

                        }
                        //是否有效 
                        else if (tFiledName.equalsIgnoreCase(Customer.STATUS)) {
                            String tStatus = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue();
                            cust.setStatus(tStatus);
                        }
                        //创建用户ID
                        else if (tFiledName.equalsIgnoreCase(Customer.CREATE_USER_ID)) {
                            String tCreateUserId = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue();
                            cust.setCreateUserId(tCreateUserId);
                        }
                        //创建用户
                        else if (tFiledName.equalsIgnoreCase(Customer.CREATE_USER)) {
                            String tCreateUser = PoiUtil.getCellValue(tHSSFRow, j, "");;
                            cust.setCreateUser(tCreateUser);
                        }
                        //单证创建时间
                        else if (tFiledName.equalsIgnoreCase(Customer.CREATE_TIME)) {
                            String tCreateTime = PoiUtil.getCellValue(tHSSFRow, j, new framework.core.DateUtil().getFullString());//tHSSFRow.getCell((short) j).getStringCellValue();
                            cust.setCreateTime(tCreateTime);
                        }
                        //最后修改人ID  
                        else if (tFiledName.equalsIgnoreCase(Customer.LAST_UPDATE_USER_ID)) {
                            String tLastUpdateUserId = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue();
                            cust.setLastUpdateUserId(tLastUpdateUserId);
                        }
                        //最后修改人
                        else if (tFiledName.equalsIgnoreCase(Customer.LAST_UPDATE_USER)) {
                            String tLastUpdateUser = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue().trim();
                            cust.setLastUpdateUser(tLastUpdateUser);
                        }
                        //最后修改时间 
                        else if (tFiledName.equalsIgnoreCase(Customer.LAST_UPDATE_TIME)) {
                            String tLastUpdateTime = PoiUtil.getCellValue(tHSSFRow, j, "");//tHSSFRow.getCell((short) j).getStringCellValue();
                            cust.setLastUpdateTime(tLastUpdateTime);
                        }
                    }      
                    tGSManufactureUploadVO.setCustomer(cust);
                    //tGSManufactureUploadVO.setFailure_reason(tFailReason);
                    tGSManufactureUploadVO.setInsert_result(String.valueOf(tResult));
                    tGSManufactureUploadVO.setRowNumber(i + 1);
                    aList.add(tGSManufactureUploadVO);
                    i++;
                }
            } catch (RuntimeException e) {
                LogWriter.error("****Manufacture BatchImport RuntimeEx :[" + e.getMessage() + "]");
                throw new GWException("导入数据中某一列数据类型不符合规定.");
            } catch (Error e) {
                LogWriter.error("****Manufacture BatchImport ErroeEx :[" + e.getMessage() + "]");
                throw new GWException(e.getMessage());
            } catch (Exception e) {
                LogWriter.error("****Manufacture BatchImport Ex :[" + e.getMessage() + "]");
                throw new GWException(e.getMessage());
            }
        }
        
        return i;
    }
   
    
    //根据参数创建要下载的Excel文件
    /**
     * return 创建文件的路径
     * 创建失败返回 "Error"
     */
    public static String createExcelByDownLoadCustomer(DBConnection con,String serverPath,String tableName,String warehouseCode,String type,String lastupdatetime)
    {
        String strFilePath = "";
        try{
            //根据参数查询数据
            Conditions cs = new Conditions();
            if (type.equals(ConstDefine.EXCELFILE_LOAD_TYPE_TIME))
            {
                String date = 
                    lastupdatetime.substring(0, 4)
                    +"-"
                    +lastupdatetime.substring(4, 6)
                    +"-"
                    +lastupdatetime.substring(6, 8)
                    +" "
                    +lastupdatetime.substring(8, 10)
                    +":"
                    +lastupdatetime.substring(10, 12)
                    +":"+lastupdatetime.substring(12,14);
                cs.add(WarehouseCategory.LAST_UPDATE_TIME +">= ?",Conditions.CUSTOM,date);
            }
            ObjectList list = Customer.fetch().search(con, cs);            
            
            //Excel生成文件保存到指定位置
            try{    
                HSSFWorkbook tWb = new HSSFWorkbook();
                HSSFSheet tSheet = tWb.createSheet(tableName);
                HSSFRow tRow = null;
           
                //表头行
                tRow = tSheet.createRow((short)0);

                HSSFCell c1 = PoiUtil.createCell(tWb,tRow,(short)0,Customer.ID);
                HSSFCell c2 = PoiUtil.createCell(tWb,tRow,(short)1,Customer.CODE);
                HSSFCell c3 = PoiUtil.createCell(tWb,tRow,(short)2,Customer.SELF_CODE);
                HSSFCell c4 = PoiUtil.createCell(tWb,tRow,(short)3,Customer.NAME);
                HSSFCell c5 = PoiUtil.createCell(tWb,tRow,(short)4,Customer.FULL_NAME);
                HSSFCell c6 = PoiUtil.createCell(tWb,tRow,(short)5,Customer.UPPER_CUSTOMER_CODE);
                HSSFCell c7 = PoiUtil.createCell(tWb,tRow,(short)6,Customer.ADDRESS);
                HSSFCell c8 = PoiUtil.createCell(tWb,tRow,(short)7,Customer.CONTACT_PERSON);
                HSSFCell c9 = PoiUtil.createCell(tWb,tRow,(short)8,Customer.CONTACT_TEL);
                HSSFCell c10 = PoiUtil.createCell(tWb,tRow,(short)9,Customer.IS_BALANCE);
                HSSFCell c11 = PoiUtil.createCell(tWb,tRow,(short)10,Customer.IS_DRAW);
                HSSFCell c12 = PoiUtil.createCell(tWb,tRow,(short)11,Customer.REMARK);
                HSSFCell c13 = PoiUtil.createCell(tWb,tRow,(short)12,Customer.WAREHOUSE_CODE);
                HSSFCell c14 = PoiUtil.createCell(tWb,tRow,(short)13,Customer.WAREHOUSE_NAME);
                HSSFCell c15 = PoiUtil.createCell(tWb,tRow,(short)14,Customer.STATUS);
                HSSFCell c16 = PoiUtil.createCell(tWb,tRow,(short)15,Customer.CREATE_USER_ID);
                HSSFCell c17 = PoiUtil.createCell(tWb,tRow,(short)16,Customer.CREATE_USER);
                HSSFCell c18 = PoiUtil.createCell(tWb,tRow,(short)17,Customer.CREATE_TIME);
                HSSFCell c19 = PoiUtil.createCell(tWb,tRow,(short)18,Customer.LAST_UPDATE_USER_ID);
                HSSFCell c20 = PoiUtil.createCell(tWb,tRow,(short)19,Customer.LAST_UPDATE_USER);
                HSSFCell c21 = PoiUtil.createCell(tWb,tRow,(short)20,Customer.LAST_UPDATE_TIME);
     
                short index = 0 ; //当前所在行号          
                short index_b = 3;
                for(int i=0;i<list.size();i++){
                    Customer cust = (Customer)list.get(i);    
          
                    //创建一行
                    tRow = tSheet.createRow((short)(index+1));
                    
                    PoiUtil.createCell(tWb,tRow,(short)0,cust.getId() + "");
                    PoiUtil.createCell(tWb,tRow,(short)1,cust.getCode() + "");
                    PoiUtil.createCell(tWb,tRow,(short)2,cust.getSelfCode() + "");
                    PoiUtil.createCell(tWb,tRow,(short)3,cust.getName() + "");
                    PoiUtil.createCell(tWb,tRow,(short)4,cust.getFullName() + "");
                    PoiUtil.createCell(tWb,tRow,(short)5,cust.getUpperCustomerCode() + "");
                    PoiUtil.createCell(tWb,tRow,(short)6,cust.getAddress() + "");
                    PoiUtil.createCell(tWb,tRow,(short)7,cust.getContactPerson() + "");
                    PoiUtil.createCell(tWb,tRow,(short)8,cust.getContactTel() + "");
                    PoiUtil.createCell(tWb,tRow,(short)9,cust.getIsBalance() + "");
                    PoiUtil.createCell(tWb,tRow,(short)10,cust.getIsDraw() + "");
                    PoiUtil.createCell(tWb,tRow,(short)11,cust.getRemark() + "");
                    PoiUtil.createCell(tWb,tRow,(short)12,cust.getWarehouseCode() + "");
                    PoiUtil.createCell(tWb,tRow,(short)13,cust.getWarehouseName() + "");
                    PoiUtil.createCell(tWb,tRow,(short)14,cust.getStatus() + "");
                    PoiUtil.createCell(tWb,tRow,(short)15,cust.getCreateUserId() + "");
                    PoiUtil.createCell(tWb,tRow,(short)16,cust.getCreateUser() + "");
                    PoiUtil.createCell(tWb,tRow,(short)17,cust.getCreateTime() + "");
                    PoiUtil.createCell(tWb,tRow,(short)18,cust.getLastUpdateUserId() + "");
                    PoiUtil.createCell(tWb,tRow,(short)19,cust.getLastUpdateUser() + "");
                    PoiUtil.createCell(tWb,tRow,(short)20,cust.getLastUpdateTime() + "");

                    index++;
                }
                String strTime = "";
                try{
                    strTime = system.imp.DateUtil.getCurrTime();
                }
                catch(Exception e)
                {
                }
                
                String fileName;
                String filePath = serverPath + ConstDefine.EXCELFILE_SAVECREATEFILE_PATH;
                fileName ="download_" + strTime + "_" + tableName + ".xls";
                FileOutputStream fileOut = new FileOutputStream(filePath+fileName);
                tWb.write(fileOut);
                
                //返回下载路径
                strFilePath = ConstDefine.EXCELFILE_DOWNLOADCREATEFILE_PATH + fileName;
                
            }catch(Exception ex){
                strFilePath = ConstDefine.EXCELFILE_CREATE_ERROR;
            }
            
        }
        catch(Exception e)
        {
            strFilePath = ConstDefine.EXCELFILE_CREATE_ERROR;
        }
        
        return strFilePath;
    }     
    /*end chengbiao.he*/
}
